package com.vsu.bookstore.util;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import com.vaadin.addon.sqlcontainer.SQLContainer;
import com.vaadin.addon.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.addon.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.addon.sqlcontainer.query.Filter;
import com.vaadin.addon.sqlcontainer.query.TableQuery;
import com.vaadin.addon.sqlcontainer.query.Filter.ComparisonType;
import com.vsu.bookstore.BookstoreApplication;

public class DatabaseHelper implements Serializable
{
  private static final long serialVersionUID = 1L;

  private JDBCConnectionPool connectionPool = null;

  private SQLContainer itemContainer = null;

  private SQLContainer categoryContainer = null;

  private final Map<Object, SQLContainer> containerMap = new HashMap<Object, SQLContainer>();

  private static DatabaseHelper Instance = null;

  public static DatabaseHelper get()
  {
    if (Instance == null)
      Instance = new DatabaseHelper();
    return Instance;
  }

  private final boolean debugMode = false;

  private DatabaseHelper()
  {
    initConnectionPool();
    initDatabase();
    initContainers();
  }

  public SQLContainer getCategoryContainer()
  {
    return categoryContainer;
  }

  public final SQLContainer getContainerByCategoryId(final Object categoryId)
  {
    if (containerMap.containsKey(categoryId))
      return containerMap.get(categoryId);
    final TableQuery tq = new TableQuery("ITEM", connectionPool);
    SQLContainer container = null;
    try
    {
      container = new SQLContainer(tq);
      container.addFilter(new Filter("Group_ID", ComparisonType.EQUALS, categoryId));
      containerMap.put(categoryId, container);
    }
    catch (final SQLException e)
    {
      e.printStackTrace();
    }
    return container;
  }

  public SQLContainer getItemContainer()
  {
    return itemContainer;
  }

  private void initConnectionPool()
  {
    try
    {
      final Properties props = new Properties();
      props.load(BookstoreApplication.class.getResourceAsStream("db.properties"));
      final StringBuilder builder = new StringBuilder();
      builder.append("jdbc:mysql://");
      builder.append(props.getProperty("server") + ":");
      builder.append(props.getProperty("port") + "/");
      builder.append(props.getProperty("dbname"));
      connectionPool = new SimpleJDBCConnectionPool(
          "com.mysql.jdbc.Driver", builder.toString(), props.getProperty("user"),
          props.getProperty("pwd"), 2, 5);
    } catch (final SQLException e)
    {
      e.printStackTrace();
    }
    catch(final IOException ex)
    {
      ex.printStackTrace();
    }
  }

  private void initContainers()
  {
    try
    {
      final TableQuery q1 = new TableQuery("ITEM", connectionPool);
      q1.setDebug(debugMode);
      itemContainer = new SQLContainer(q1);
      itemContainer.setDebugMode(debugMode);

      final TableQuery q2 = new TableQuery("CATEGORY", connectionPool);
      q2.setDebug(debugMode);
      categoryContainer = new SQLContainer(q2);
      categoryContainer.setDebugMode(debugMode);
    } catch (final SQLException e)
    {
      e.printStackTrace();
    }
  }

  private void initDatabase()
  {
    try
    {
      final Connection conn = connectionPool.reserveConnection();
      final Statement statement = conn.createStatement();
      statement.executeQuery("SELECT * FROM ITEM");
      statement.executeQuery("SELECT * FROM CATEGORY");
      statement.close();
      conn.commit();
      connectionPool.releaseConnection(conn);
    } catch (final SQLException e)
    {
      e.printStackTrace();
    }
  }

  public void updateRating(final Object id, final Integer voted, final Double points)
  {
    Connection conn = null;
    PreparedStatement statement = null;
    try
    {
      conn = connectionPool.reserveConnection();
      statement =
        conn.prepareStatement("UPDATE ITEM SET Voted = ?, RatingPoints = ? WHERE ID = ?");
      statement.setInt(1, voted);
      statement.setDouble(2, points);
      statement.setInt(3, (Integer)id);
      statement.execute();
      statement.close();
      conn.commit();
    }
    catch (final SQLException e)
    {
      e.printStackTrace();
    }
    finally
    {
      connectionPool.releaseConnection(conn);
    }
  }
}